#include "dbPrescription.h"

dbPrescription::dbPrescription()
{

}

//创建表
void dbPrescription::createTable()
{
	QString strSql = "create table medicineRecord(id INTEGER PRIMARY KEY, name VARCHAR(200), sum INTEGER, unit varchar(200), date DATE);";
	QSqlQuery query(sqliteOperate::m_db);
	query.exec(strSql);//建表

	strSql = "create table medicineList(id INTEGER PRIMARY KEY, name VARCHAR(200), default_sum INTEGER, unit varchar(200));";
	//QString strIndexSql = "CREATE INDEX MedicineRecordIndex1 on medicineRecord(id);";
	bool bResult = query.exec(strSql); //创建索引
}

//添加一图书
void dbPrescription::addMedicineRecord(const QString& name, const int sum, QString unit, QDate date)
{
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("INSERT INTO medicineRecord(name, sum, unit, date) VALUES (:name, :sum, :unit, :date)"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":name",name);
	query.bindValue(":sum",sum);
	query.bindValue(":unit", unit);
	query.bindValue(":date",date);

	bool bResult = query.exec();
	qDebug() << "addMedicineRecord without id,result=" << bResult;

	if (getMedicineListByName(name).length() <= 0) {
		//未保存为默认设置, 自动添加
		this->addMedicineList(name, sum, unit);
	}
}

//添加一图书
void dbPrescription::addMedicineRecord(int id,const QString& name, const int sum, QString unit, QDate date = QDate::currentDate())
{
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("INSERT INTO medicineRecord(id, name, sum, unit, date) VALUES (:id, :name, :sum, :unit, :date)"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":id",id);
	query.bindValue(":name",name);
	query.bindValue(":sum",sum);
	query.bindValue(":unit", unit);
	query.bindValue(":date",date);

	bool bResult = query.exec();
	qDebug() << "addMedicineRecord,result=" << bResult;
	if (getMedicineListByName(name).length() <= 0) {
		//未保存为默认设置, 自动添加
		this->addMedicineList(name, sum, unit);
	}
}

///
/// \brief dbPrescription::getMedicineRecords
/// \return
/// 获取所有的记录
QList<record_t> dbPrescription::getMedicineRecords(bool detial)
{
	QList<record_t> records;
	record_t record_one;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	if (detial)
		strSql.append("SELECT id, name, sum, unit, date FROM medicineRecord order by id desc;"); //需要绑定的字段
	else
		strSql.append("SELECT id, name, sum(sum) as sum, unit, max(date) as date FROM medicineRecord group by name order by id desc;"); //需要绑定的字段
	query.prepare(strSql);
	bool bResult = query.exec();
	while (query.next()) {
		record_one.id	= query.value("id").toInt();
		record_one.name = query.value("name").toString();
		record_one.sum	= query.value("sum").toInt();
		record_one.unit	= query.value("unit").toString();
		record_one.date = query.value("date").toDate();
		records.append(record_one);
	}

	qDebug() << "get all records,result=" << bResult<<"sum:"<<records.length();
	return records;
}

///
/// \brief dbPrescription::getMedicineRecordsByGroup
/// \return
/// 获取分组记录
QList<record_t> dbPrescription::getMedicineRecordsByGroup(QString group, bool detial)
{
	QList<record_t> records;
	record_t record_one;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	QString filter;
	if (group == "today") {
		QDate dat(QDate::currentDate().addDays(-1));
		filter = dat.toString("yyyy-MM-dd");
	}
	else if (group == "month") {
		QDate dat(QDate::currentDate().addMonths(-1));
		filter = dat.toString("yyyy-MM-")+QString::number(dat.daysInMonth());
	}
	else if (group == "year") {
		QDate dat(QDate::currentDate().addYears(-1));
		filter = dat.toString("yyyy-12-31");
	}
	else {
		filter = "0000-00-00";
	}
	if (detial)
		strSql.append("SELECT * FROM medicineRecord where date>\""+filter+"\" order by id desc;"); //需要绑定的字段
	else
		strSql.append("SELECT id,name,sum(sum) as sum,unit,max(date) as date FROM medicineRecord where date>\""+filter+"\" group by name order by id desc;");

	query.prepare(strSql);
	bool bResult = query.exec();
	while (query.next()) {
		record_one.id	= query.value("id").toInt();
		record_one.name = query.value("name").toString();
		record_one.sum	= query.value("sum").toInt();
		record_one.unit	= query.value("unit").toString();
		record_one.date = query.value("date").toDate();
		records.append(record_one);
	}

	qDebug() << "get all records,result=" << bResult<<"sum:"<<records.length();
	return records;
}

///
/// \brief dbPrescription::getMedicineLists
/// \return
/// 获取默认选项
QList<default_list_t> dbPrescription::getMedicineLists()
{
	QList<default_list_t> lists;
	default_list_t list_one;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("SELECT * FROM medicineList;"); //需要绑定的字段
	query.prepare(strSql);
	bool bResult = query.exec();
	while (query.next()) {
		list_one.id				= query.value("id").toInt();
		list_one.name			= query.value("name").toString();
		list_one.default_sum	= query.value("default_sum").toInt();
		list_one.unit			= query.value("unit").toString();
		lists.append(list_one);
	}

	qDebug() << "get all lists,result=" << bResult<<"sum:"<<lists.length();
	return lists;
}

///
/// \brief dbPrescription::getMedicineLists
/// \return
/// 根据名字获取默认选项
QList<default_list_t> dbPrescription::getMedicineListByName(QString name)
{
	QList<default_list_t> lists;
	default_list_t list_one;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("SELECT * FROM medicineList where name=:name;"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":name", name);
	bool bResult = query.exec();
	while (query.next()) {
		list_one.id				= query.value("id").toInt();
		list_one.name			= query.value("name").toString();
		list_one.default_sum	= query.value("default_sum").toInt();
		list_one.unit			= query.value("unit").toString();
		lists.append(list_one);
	}

	qDebug() << "get all lists by name,result=" << bResult<<"sum:"<<lists.length();
	return lists;
}


///
/// \brief dbPrescription::getMedicineLists
/// \return
/// 获取默认选项de 名字
QStringList dbPrescription::getMedicineListsNames()
{
	QStringList lists;
	QString list_one;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("SELECT distinct name FROM medicineList order by name;"); //需要绑定的字段
	query.prepare(strSql);
	bool bResult = query.exec();
	while (query.next()) {
		list_one = query.value("name").toString();
		lists.append(list_one);
	}

	qDebug() << "get all lists name,result=" << bResult<<"sum:"<<lists.length();
	return lists;
}

///
/// \brief dbPrescription::getUnitByName
/// \param name
/// \return
/// 根据名称获取药的单位
QStringList dbPrescription::getUnitByName(QString name)
{
	QStringList units;
	QString unit;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("SELECT unit FROM medicineList where name=:name;"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":name", name);
	bool bResult = query.exec();
	while (query.next()) {
		unit = query.value("unit").toString();
		units.append(unit);
	}

	qDebug() << "get units,result=" << bResult<<"sum:"<<units.length();
	return units;
}

///
/// \brief dbPrescription::getDefaultSumByName
/// \param name
/// \return
///根据名称获取要的默认数量
int dbPrescription::getDefaultSumByName(QString name)
{
	int sum = 1;
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("SELECT default_sum FROM medicineList where name=:name;"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":name", name);
	bool bResult = query.exec();
	if (query.next()) {
		sum = query.value("default_sum").toInt();
	}

	qDebug() << "get defaut sum,result=" << bResult<<"value:"<<sum;
	return sum;
}

//根据id删除图书
bool dbPrescription::removeMedicineRecord(const int id)
{
	QString strSql = QString("DELETE FROM medicineRecord WHERE id = '%1'").arg(id);
	QSqlQuery query(sqliteOperate::m_db);
	bool bResult = query.exec(strSql);

	qDebug() << "removeMedicineRecord,result=" << bResult;
	return bResult;
}

//根据id更新图书名
void dbPrescription::updateMedicineRecordSum(const QString& id, const int sum)
{
	QString strSql = "UPDATE medicineRecord SET sum = :sum WHERE id = :id";
	QSqlQuery query(sqliteOperate::m_db);
	query.prepare(strSql);
	query.bindValue(":id",id);
	query.bindValue(":sum",sum);
	bool bReuslt = query.exec();
}

//根据图书名找图书(模糊搜索)
QStringList dbPrescription::searchByMedicineRecord(const QString& name)
{
	QStringList medicineRecordIdList;
	QSqlQuery query(sqliteOperate::m_db);
	QString strName = name;
	strName.replace("%","!%");
	const QString strSqlTemplate = "SELECT * FROM medicineRecord WHERE name like '%:name%' ESCAPE '!'";//sql语句模板
	QString strSql = strSqlTemplate;
	strSql.replace(":name",name);
	query.exec(strSql);

	while(query.next())
	{
		QString id = query.value(0).toString();//图书id
		medicineRecordIdList.append(id);
	}
	return medicineRecordIdList;
}




//添加默认选项
bool dbPrescription::addMedicineList(const QString& name, const int default_sum, QString unit)
{
	QString strSql;
	QSqlQuery query(sqliteOperate::m_db);
	strSql.append("INSERT INTO medicineList(name, default_sum, unit) VALUES (:name, :default_sum, :unit)"); //需要绑定的字段
	query.prepare(strSql);
	query.bindValue(":name",name);
	query.bindValue(":default_sum",default_sum);
	query.bindValue(":unit",unit);

	bool bResult = query.exec();
	qDebug() << "addMedicineList without id,result=" << bResult;
	return bResult;
}


//删除默认选项
bool dbPrescription::removeMedicineList(const int id)
{
	QString strSql = QString("DELETE FROM medicineList WHERE id = %1").arg(id);
	QSqlQuery query(sqliteOperate::m_db);
	bool bResult = query.exec(strSql);

	qDebug() << "removeMedicineRecord,result=" << bResult;
	return bResult;
}

//更新默认选项
void dbPrescription::updateMedicineList(const QString& id, const int sum)
{
	QString strSql = "UPDATE medicineList SET sum = :sum WHERE id = :id";
	QSqlQuery query(sqliteOperate::m_db);
	query.prepare(strSql);
	query.bindValue(":id",id);
	query.bindValue(":sum",sum);
	bool bReuslt = query.exec();
}
